﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace SHomeWorkshop.LunarSF.StudentInfosManager
{
    /// <summary>
    /// 备用。尽可能使用 SQLiteHelper 类。
    /// 此类修改自：
    /// http://git.oschina.net/2467567137/SQLiteDAL/blob/master/SQLiteDAL/SQLite.cs?dir=0&filepath=SQLiteDAL%2FSQLite.cs&oid=ef353c52f312e48c7b836d7aca027017866800d9&sha=6ee7e7994f23da31f8ce6e2e2878d32abd231894
    /// </summary>
    public class SQLiteDBManager
    {
        private string dataBaseFilePath;

        public string DataBaseFilePath
        {
            get { return dataBaseFilePath; }
        }

        private string connectionString = string.Empty;

        /// <summary> 
        /// 构造函数 
        /// </summary> 
        /// <param name="dbPath">SQLite数据库文件路径</param> 
        public SQLiteDBManager(string dbPath)
        {
            dataBaseFilePath = dbPath;
            this.connectionString = "Data Source=" + dbPath + ";UserId=Admin;Password=DyBj#PpBb;";
        }

        /// <summary> 
        /// 创建SQLite数据库文件 
        /// </summary> 
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param> 
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath + ";UserId=Admin;Password=DyBj#PpBb;"))
            {
                connection.Open();
                //using (SQLiteCommand command = new SQLiteCommand(connection))
                //{
                //    command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                //    command.ExecuteNonQuery();
                //    command.CommandText = "DROP TABLE Demo";
                //    command.ExecuteNonQuery();
                //}
            }
        }

        /// <summary>
        /// 数据库中指定名称的表是否存在。
        /// </summary>
        /// <param name="tableName">表名称。</param>
        /// <returns></returns>
        public bool TableExist(string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return false;

            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dataBaseFilePath + ";UserId=Admin;Password=DyBj#PpBb;"))
            {
                connection.Open();
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = $"SELECT COUNT(*) FROM sqlite_master where type='table' and name='{tableName}';";
                if (0 == Convert.ToInt32(command.ExecuteScalar()))
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }
        /// <summary>
        /// 插入一条记录。
        /// </summary>
        /// <param name="commandText">例如：
        /// "insert into 表名 values(@id, @name, @sex)"
        /// 注意表结构。
        /// </param>
        /// <param name="sqliteParameters">例如：
        /// new[] {            //添加参数
        ///        new SQLiteParameter("@id", i),
        ///        new SQLiteParameter("@name", "中国人"),
        ///        new SQLiteParameter("@sex", "男")
        ///        }
        /// 注意表结构。
        /// </param>
        public int InsertRecord(string commandText, SQLiteParameter[] sqliteParameters)
        {
            var result = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();//打开连接
                using (SQLiteTransaction transsaction = connection.BeginTransaction())//实例化一个事务
                {
                    SQLiteCommand cmd = new SQLiteCommand(connection);//实例化SQL命令
                    cmd.Transaction = transsaction;
                    cmd.CommandText = commandText;//设置带参SQL语句
                    cmd.Parameters.AddRange(sqliteParameters);
                    result = cmd.ExecuteNonQuery();//执行查询
                    transsaction.Commit();//提交

                }
            }

            return result;
        }

        /// <summary>
        /// 批量插入记录，速度快。
        /// </summary>
        /// <param name="commandText">例如：
        /// "insert into 表名 values(@id, @name, @sex)"
        /// 注意表结构。
        /// </param>
        /// <param name="sqliteParameters">例如：
        /// new[] {            //添加参数
        ///        new SQLiteParameter("@id", i),
        ///        new SQLiteParameter("@name", "中国人"),
        ///        new SQLiteParameter("@sex", "男")
        ///        }
        /// 注意表结构。
        /// </param>
        public void InsertRecords(string commandText, SQLiteParameter[] sqliteParameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();//打开连接
                using (SQLiteTransaction transsaction = connection.BeginTransaction())//实例化一个事务
                {
                    for (int i = 0; i < sqliteParameters.Length; i++)
                    {
                        SQLiteCommand cmd = new SQLiteCommand(connection);//实例化SQL命令
                        cmd.Transaction = transsaction;
                        cmd.CommandText = commandText;//设置带参SQL语句
                        cmd.Parameters.AddRange(sqliteParameters);
                        cmd.ExecuteNonQuery();//执行查询
                    }
                    transsaction.Commit();//提交
                }
            }
        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }

        }

        /// <summary> 
        /// 执行一个查询语句，返回查询结果的第一行第一列 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        /// <summary> 
        /// 查询数据库中的所有数据类型信息 
        /// </summary> 
        /// <returns></returns> 
        public DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns) 
                //{ 
                //        Console.WriteLine(column.ColumnName); 
                //} 
                return data;
            }
        }
    }
}
